let express = require('express');
let router = express.Router();
let myQuery = require('../tools/db');
let {apiUrl} = require('../tools/config')

// 城市
router.get('/area', async(req,res,next)=>{
    let sql = `SELECT * FROM e_area`;
    let [err,result] = await myQuery(sql);
    if(err){
        next({code:500,msg:err})
    }
    res.send({code:200,msg:'ok',result})
})
// 年级
router.get('/grade', async(req,res,next)=>{
    let sql = `SELECT * FROM e_grade`;
    let [err,result] = await myQuery(sql);
    if(err){
        next({code:500,msg:err})
    }
    res.send({code:200,msg:'ok',result})
})
// 学科
router.get('/subject', async(req,res,next)=>{
    let sql = `SELECT * FROM e_subject`;
    let [err,result] = await myQuery(sql);
    if(err){
        next({code:500,msg:err})
    }
    res.send({code:200,msg:'ok',result})
})
// 版本
router.get('/ver', async(req,res,next)=>{
    let sql = `SELECT * FROM e_type`;
    let [err,result] = await myQuery(sql);
    if(err){
        next({code:500,msg:err})
    }
    res.send({code:200,msg:'ok',result})
})
// 分页
router.get('/coureslist', async(req,res,next)=>{
    let {pagenum=6,page=1,type=1,area = '',grade='',subject='',ver=''}=req.query;
    // 筛选时 判断是否有条件
    let wherestr = ''
    if(area != ''){
        wherestr += ` AND area_name ='${area}' `
    }
    if(grade != ''){
        wherestr += ` AND area_name ='${grade}' `
    }
    if(subject != ''){
        wherestr += ` AND area_name ='${subject}' `
    }
    if(ver != ''){
        wherestr += ` AND area_name ='${ver}' `
    }
    // 获取总条数
    let sql = `SELECT COUNT(*) AS totle FROM e_course  WHERE type=${type}${wherestr}`;
    
    // 取出总条数
    let [,[{totle}]] = await myQuery(sql);
    // console.log(totle);
    // 获取起始下标条数
    let stactIndex = (page-1)*pagenum;
    
    // 获取总页数
    let allpage = Math.ceil(totle/pagenum)
    

    // 课程总页数
    let sql1 = `SELECT cid,price,title,type,area_name,grade_name,subject_name,tid,CONCAT('${apiUrl}','course/',image_src) AS imgge_src FROM e_course WHERE type=${type}${wherestr} LIMIT ${stactIndex},${pagenum}`
    let [err,result] = await myQuery(sql1);

    if(err){
        next({code:500,msg:err})
    }
    res.send({code:200,msg:'ok',pagenum,page,type,stactIndex,allpage,result})
})

module.exports = router;